﻿using System;
using System.Data;
using System.Data.OleDb;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.Security;




namespace GMS.BIZ
{
    public class Import
    {
        
        public Import()
        {
           
        }

       /// <summary>
       /// 导入包含初始化学生信息的excel文件
       /// </summary>
       /// <param name="execlfilename">excel文件名</param>
       /// <returns>反馈信息</returns>
        public static string ImportExcel(string execlfilename)
        {
            string error = string.Empty;
            DataTable dtExcel = ExcelToDataTable(@execlfilename, "Sheet1");
             if (dtExcel!=null) 
            for (int i = 0; i < dtExcel.Rows.Count; i++)
            {
                error = error + InsertToDB(dtExcel.Rows[i][0].ToString().Trim(), dtExcel.Rows[i][1].ToString().Trim(), dtExcel.Rows[i][2].ToString().Trim(), dtExcel.Rows[i][3].ToString().Trim(), dtExcel.Rows[i][4].ToString().Trim());
                
            }
            if (error!=string.Empty) return error;
            return "批量导入成功！";
        }


        protected static DataTable ExcelToDataTable(string strExcelFileName, string strSheetName)
        {
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + HttpContext.Current.Server.MapPath("importfiles") + "\\" + strExcelFileName + ";" + "Extended Properties='Excel 8.0;HDR=YES'";
            string strExcel = string.Format("select * from [{0}$]", strSheetName);
            DataSet ds = new DataSet();
            using (OleDbConnection conn = new OleDbConnection(strConn))
            {
                conn.Open();
                OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, strConn);
                adapter.Fill(ds, strSheetName);
                conn.Close();
            }

            return ds.Tables[strSheetName];
        }


        protected static string InsertToDB(string stdid, string stdpwd, string stdname, string classid, string gradyear)
        {
            StudentInfo mySI = new StudentInfo();
            mySI.StdID = stdid;
            mySI.StdName = stdname;
            Library myLb = new Library();
            string temp = myLb.AddClass(classid,null,null);
            mySI.ClassID = classid;
            mySI.GraduateYear = gradyear;            
            string message1 = myLb.AddStudent(mySI),message2;
            if (message1 != ErrorMessage.OK) return stdid + message1;
            else
            {
                message2 = Account.Add(stdid, stdpwd, stdid, false, false, false, true);
                if (message2 != ErrorMessage.OK) return stdid+message2;
            }
            return string.Empty;
        }
    }
}
